Published on

MySQL 高级功能详解：视图、存储过程、函数、触发器与事件调度实践指南

Authors
  • avatar
    Name
    Liant
    Twitter

MySQL 高级功能详解：视图、存储过程、函数、触发器与事件调度实践指南

视图

a.什么是视图？

视图是存储查询，调用时会生成结果集。

视图充当虚拟表，本身不存放任何数据。

b.视图有哪些应用场景？

1.简化SQL查询

2.数据权限

3.兼容不同版本的表结构

c.语法

    VIEW

d.算法

1.Merge 合并算法
2.Temptable 临时表算法
3.Undefined 未定义（默认）
view-algorithm

存储代码

a.基础语法

语法说明
DECLARE变量声明
DEFAULT默认
SET变量赋值
SELECT INTO查询结果存储在变量中
DEALLOCATE PREPARE释放定义
ON DUPLICATE KEY UPDATE插入更新语句
DECLARE (EXIT / CONTINUE) HANDLER FOR声明异常处理
SQL_CALC_FOUND_ROWS / FOUND_ROWS()查询结果集的记录数
PREPARE (SQL) FROM (NAME)定义预处理语句
EXECUTE (NAME)执行预处理语句

a.1.优点

procedure-advantage.png

a.2.缺点

procedure-shortcoming.png

a.3 推荐工具

Devart dbForge Studio for MySQL

b.函数

b.1.语法

语法说明
FUNCTION关键字
RETURNS定义返回值类型
[no] DETERMINISTIC决定性：表示一个函数在输入不变的情况下输出是否确定。如果你的函数当输入一样时,会返回同样的结果.这样, 数据库就用前一个计算的值,而不需要再重新计算一次.这对于使用函数索引等,会得到相当大的好处.

b.2.案例：实现获取中文日期的函数

实现思路
    1.使用 WEEKDAY 获取星期
    2.结果转为中文并返回

c.存储过程

c.1.语法

语法说明
PROCEDURE关键字
IN入参
OUT出参
LEAVE离开，不再继续往下执行

c.2.效率

procedure-efficiency.png

d.触发器

d.1.简介

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发。

比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

BEFORE | AFTER 是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。

触发器经常用于加强数据的完整性约束和业务规则等。

有一个非常好的特性就是触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。

触发程序视为单一交易中的一部份，因此可以由原触发程序还原交易。

如果在交易过程中侦测到严重的错误（如使用者中断连线），则会自动还原整个交易。

他的作用很明显了，可以保重数据的完整性。

触发器虽然能解决好多问题，但在高并发场景下，最好不要用触发器，触发器会影响数据库的性能，可以在代码中实现相关逻辑。

还会带来维护上的一些问题。

e.事件

事件可调用一次，也可周期性调用，由特定线程管理，统称“事件调度器”

e.1.语法

语法说明
show variables like 'event_scheduler'查询事件调度器状态
set global event_scheduler=on;开启事件(临时,重启mysql失效)
ALTER EVENT e_event_name ON COMPLETION PRESERVE ENABLE;开启某个事件
SHOW processlist;查看调度器线程
SHOW events;查看事件